package de.slothsoft.wasbinich.android.database.local;

import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class SqlPictureHelper implements SqlTableHelper {

    public static final String TABLE_NAME = "picture"; //$NON-NLS-1$

    public static final String COLUMN_ID = "id"; //$NON-NLS-1$
    public static final String COLUMN_SECTION = "section"; //$NON-NLS-1$
    public static final String COLUMN_BIG_PICTURE = "bigPicture"; //$NON-NLS-1$
    public static final String COLUMN_CORRECT_ANSWERS_EN = "correctAnswers_en"; //$NON-NLS-1$
    public static final String COLUMN_CORRECT_ANSWERS_DE = "correctAnswers_de"; //$NON-NLS-1$
    public static final String COLUMN_DESCRIPTION_DE = "description_de"; //$NON-NLS-1$
    public static final String COLUMN_DESCRIPTION_EN = "description_en"; //$NON-NLS-1$
    public static final String COLUMN_PHOTOGRAPHER = "photographer"; //$NON-NLS-1$

    public static final String[] COLUMNS_FULL_PICTURE = { COLUMN_ID, COLUMN_SECTION, COLUMN_BIG_PICTURE,
	    COLUMN_CORRECT_ANSWERS_EN, COLUMN_DESCRIPTION_EN, COLUMN_CORRECT_ANSWERS_DE, COLUMN_DESCRIPTION_DE };

    public static final String[] COLUMNS_PICTURE_EN = { COLUMN_ID, COLUMN_SECTION, COLUMN_BIG_PICTURE,
	    COLUMN_CORRECT_ANSWERS_EN, COLUMN_DESCRIPTION_EN };
    public static final String[] COLUMNS_PICTURE_DE = { COLUMN_ID, COLUMN_SECTION, COLUMN_BIG_PICTURE,
	    COLUMN_CORRECT_ANSWERS_DE, COLUMN_DESCRIPTION_DE };

    private static final String DATABASE_CREATE = //
    "CREATE TABLE " + TABLE_NAME + " (" + //$NON-NLS-1$ //$NON-NLS-2$
	    "id integer PRIMARY KEY AUTOINCREMENT," + //$NON-NLS-1$
	    "section text," + //$NON-NLS-1$
	    "bigPicture text," + //$NON-NLS-1$
	    "correctAnswers_en text," + //$NON-NLS-1$
	    "description_en text," + //$NON-NLS-1$
	    "correctAnswers_de text," + //$NON-NLS-1$
	    "description_de text," + //$NON-NLS-1$
	    "photographer text" + //$NON-NLS-1$
	    ");"; //$NON-NLS-1$

    @Override
    public void onCreate(SQLiteDatabase database) {
	database.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	Log.w("slothsoft", "Upgrading table " + TABLE_NAME + " from version " + oldVersion + " to " + newVersion //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		+ ", which will destroy all old data"); //$NON-NLS-1$
	db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); //$NON-NLS-1$
	onCreate(db);
    }

}